package nix.hibernate;

import java.util.Arrays;
import java.util.List;

import nix.domain.Customer;
import nix.domain.LinkMan;
import nix.utils.HibernateUtils;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

public class DemoHQL {

	@Test
	public void fun1() {		
		Session session = HibernateUtils.getCurrentSession();
		Transaction ts = session.beginTransaction();
		// 没有重名则可使用简单类名
		String hql = "from nix.hibernate.User";
		Query query = session.createQuery(hql);
		List<User> list = query.list();
		System.out.println(list);
		
		ts.commit();
	}
	
	@Test
	public void fun2() {		
		Session session = HibernateUtils.getCurrentSession();
		Transaction ts = session.beginTransaction();
		// 条件中使用属性名
		String hql = "from nix.hibernate.User where id = 1";
		Query query = session.createQuery(hql);
		User user = (User) query.uniqueResult();
		System.out.println(user);
		
		ts.commit();
	}
	
	@Test
	public void fun3() {		
		Session session = HibernateUtils.getCurrentSession();
		Transaction ts = session.beginTransaction();
		// 使用?占位符
		String hql = "from nix.hibernate.User where id = ?";
		Query query = session.createQuery(hql);
		// query.setInteger(0, 1);
		query.setParameter(0, 1);
		User user = (User) query.uniqueResult();
		System.out.println(user);
		
		ts.commit();
	}
	
	@Test
	public void fun4() {		
		Session session = HibernateUtils.getCurrentSession();
		Transaction ts = session.beginTransaction();
		// 使用命名占位符
		String hql = "from nix.hibernate.User where id = :cust_id";
		Query query = session.createQuery(hql);
		query.setParameter("cust_id", 1);
		User user = (User) query.uniqueResult();
		System.out.println(user);
		
		ts.commit();
	}
	
	@Test
	public void fun5() {		
		Session session = HibernateUtils.getCurrentSession();
		Transaction ts = session.beginTransaction();

		String hql = "from nix.hibernate.User";
		Query query = session.createQuery(hql);
		// limit分页设置
		query.setFirstResult(0);
		query.setMaxResults(2);
		List<User> list = query.list();
		System.out.println(list);
		
		ts.commit();
	}
	
	@Test
	public void fun6() {		
		Session session = HibernateUtils.getSession();
		Transaction ts = session.beginTransaction();
		// 排序
		String hql = "from nix.hibernate.User order by id desc";
		Query query = session.createQuery(hql);
		List<User> list = query.list();
		System.out.println(list);
		
		ts.commit();
		session.close();
	}
	
	@Test
	public void fun7() {		
		Session session = HibernateUtils.getSession();
		Transaction ts = session.beginTransaction();
		// 聚合
		// String hql = "select count(*) from nix.hibernate.User";
		// String hql = "select sum(id) from nix.hibernate.User";
		String hql = "select avg(id) from nix.hibernate.User";
		// String hql = "select max(id) from nix.hibernate.User";
		// String hql = "select min(id) from nix.hibernate.User";
		Query query = session.createQuery(hql);
		Number number = (Number) query.uniqueResult();
		System.out.println(number);

		ts.commit();
		session.close();
	}
	
	@Test
	public void fun8() {		
		Session session = HibernateUtils.getSession();
		Transaction ts = session.beginTransaction();
		// 投影
		String hql = "select name from nix.hibernate.User";
		Query query = session.createQuery(hql);
		List list = query.list();
		System.out.println(list);

		ts.commit();
		session.close();
	}
	
	@Test
	public void fun9() {		
		Session session = HibernateUtils.getSession();
		Transaction ts = session.beginTransaction();
		// 投影，使用构造函数new对象（实体类中必须有对应构造函数）
		String hql = "select new User(id,name) from nix.hibernate.User";
		Query query = session.createQuery(hql);
		List list = query.list();
		System.out.println(list);

		ts.commit();
		session.close();
	}
	
	@Test
	public void fun10() {		
		Session session = HibernateUtils.getSession();
		Transaction ts = session.beginTransaction();
		// 内连接，返回两个对象的数组
		String hql = "from nix.domain.Customer c inner join c.linkMens";
		Query query = session.createQuery(hql);
		List<Object[]> list = query.list();
		for (Object[] arr : list) {
			System.out.println(Arrays.toString(arr));
		}
	
		ts.commit();
		session.close();
	}
	
	
	@Test
	public void fun11() {		
		Session session = HibernateUtils.getSession();
		Transaction ts = session.beginTransaction();
		// 迫切内连接，返回装配好关系的对象集合
		String hql = "from nix.domain.Customer c inner join fetch c.linkMens";
		Query query = session.createQuery(hql);
		List<Customer> list = query.list();
		System.out.println(list);
	
		ts.commit();
		session.close();
	}
	
	@Test
	public void fun12() {		
		Session session = HibernateUtils.getSession();
		Transaction ts = session.beginTransaction();
		// 左外连接
		String hql = "from nix.domain.Customer c left join c.linkMens";
		Query query = session.createQuery(hql);
		List<Object[]> list = query.list();
		for (Object[] arr : list) {
			System.out.println(Arrays.toString(arr));
		}
	
		ts.commit();
		session.close();
	}
	
	@Test
	public void fun13() {		
		Session session = HibernateUtils.getSession();
		Transaction ts = session.beginTransaction();
		// 迫切左外连接
		String hql = "from nix.domain.Customer c left join fetch c.linkMens";
		Query query = session.createQuery(hql);
		List<Customer> list = query.list();
		System.out.println(list);
	
		ts.commit();
		session.close();
	}
	
	@Test
	public void fun14() {		
		Session session = HibernateUtils.getSession();
		Transaction ts = session.beginTransaction();
		// 右外连接
		String hql = "from nix.domain.Customer c right join c.linkMens";
		Query query = session.createQuery(hql);
		List<Object[]> list = query.list();
		for (Object[] arr : list) {
			System.out.println(Arrays.toString(arr));
		}
		
		ts.commit();
		session.close();
	}
	
	@Test
	public void fun15() {		
		Session session = HibernateUtils.getSession();
		Transaction ts = session.beginTransaction();
		// 迫切右外连接
		String hql = "from nix.domain.Customer c right join fetch c.linkMens";
		Query query = session.createQuery(hql);
		List<Customer> list = query.list();
		System.out.println(list);
		
		ts.commit();
		session.close();
	}
}
